package com.boco.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.boco.entity.EMR_ZY_FYMX;

public class GetEMR_ZY_FYMXData {
	
	private static final Log log = LogFactory.getLog(GetEMR_ZY_FYMXData.class);
	public static List<EMR_ZY_FYMX> getData()
	{
	    Connection con = null;
	    PreparedStatement pre = null;
	    ResultSet result = null;
	    List<EMR_ZY_FYMX> re = new ArrayList<EMR_ZY_FYMX>();
	    try
	    {
	        Class.forName("oracle.jdbc.driver.OracleDriver");
	        System.out.println("开始尝试连接数据库！");
	        String url = "jdbc:oracle:" + "thin:@172.20.21.12:1521:orcl";
	        String user = "sjzx_hxk";
	        String password = "boco#1234";
	        con = DriverManager.getConnection(url, user, password);
	        System.out.println("连接成功！");
	        
	        String sql = "select "
	        		+"'' as HZZSYH,"
					+"'' as DQMC,"
					+"'' as DQDM,"
					+"'' as ZZJGMC,"
					+"a.yljgdm as ZZJGDM,"
					+"'' as SJMJ,"
					+"b.zyh as ZYH,"
					+"c.bah as BAH,"
					+"'' as YEID_HIS,"
					+"'' as JLH_HIS,"
					+"'' as ZYKSDM,"
					+"'' as ZYKSMC,"
					+"b.ryksbm as KSDM_HIS,"
					+"b.ryksmc as KSMC_HIS,"
					+"'' as XXLY,"
					+"'' as ZYYZ,"
					+"a.sfyp as XMFLDM,"
					+"'' as ZYFYLBMC,"
					+"'' as ZYFYLBDM,"
					+"a.xmflbm as XMDM,"
					+"a.xmflmc as DMMC,"
					+"a.mxxmbm as XMDM_HIS,"
					+"a.mxxmmc as XMMC_HIS,"
					+"a.mxxmdw as XMDW,"
					+"a.mxxmdj as XMDJ,"
					+"a.mxxmsl as XMSL,"
					+"a.mxxmssje as XMJE,"
					+"to_char(a.fyfssj,'YYYY-MM-dd HH24:mi:ss') as CJSJ,"
					+"'1900-01-01 00:00:00' as JFSJ,"
					+"to_char(a.sfjssj,'YYYY-MM-dd HH24:mi:ss') as JSSJ,"
					+"'' as JSCS,"
					+"'' as JSPHJ,"
					+"'' as SFKSS,"
					+"'' as SFGJJY,"
					+"'' as SFJMYP,"

					+"to_char(a.ywscsj,'YYYY-MM-dd HH24:mi:ss') as BCRQ,"
					+"'' as ZRLSH,"
					+"'' as ORGCODE,"
					+"'' as AREACODE,"
					+"to_char(sysdate,'yyyyMMddHH24miss') as UPLOADTIME,"
					+"'' as  SOURCEID "
					+" from tb_zy_sfmxb a left join tb_zy_rydjb b "
					+" on a.yljgdm = b.yljgdm and a.kh = b.kh"
					+" and a.klx = b.klx left join tb_ba_jbxx c "
					+" on a.yljgdm = c.yljgdm and a.kh = c.kh "
					+" and a.klx = c.klx ";
	 
	        log.info("查询sql："+sql);
	        pre = con.prepareStatement(sql);
	        result = pre.executeQuery();
	        System.out.println("执行成功");
	        while (result.next()){
//	            System.out.println(result.getInt("YYDAH"));
	        	EMR_ZY_FYMX temp = new EMR_ZY_FYMX();
	        	temp.setHZZSYH(result.getString("HZZSYH"));
	        	temp.setDQMC(result.getString("DQMC"));
	        	temp.setDQDM(result.getString("DQDM"));
	        	temp.setZZJGMC(result.getString("ZZJGMC"));
	        	temp.setZZJGDM(result.getString("ZZJGDM"));
	        	temp.setSJMJ(result.getString("SJMJ"));
	        	temp.setZYH(result.getString("ZYH"));
	        	temp.setBAH(result.getString("BAH"));
	        	temp.setYEID_HIS(result.getString("YEID_HIS"));
	        	temp.setJLH_HIS(result.getString("JLH_HIS"));
	        	temp.setZYKSDM(result.getString("ZYKSDM"));
	        	temp.setZYKSMC(result.getString("ZYKSMC"));
	        	temp.setKSDM_HIS(result.getString("KSDM_HIS"));
	        	temp.setKSMC_HIS(result.getString("KSMC_HIS"));
	        	temp.setXXLY(result.getString("XXLY"));
	        	temp.setZYYZ(result.getString("ZYYZ"));
	        	temp.setXMFLDM(result.getString("XMFLDM"));
	        	temp.setZYFYLBMC(result.getString("ZYFYLBMC"));
	        	temp.setZYFYLBDM(result.getString("ZYFYLBDM"));
	        	temp.setXMDM(result.getString("XMDM"));
	        	
	        	temp.setDMMC(result.getString("DMMC"));
	        	temp.setXMDM_HIS(result.getString("XMDM_HIS"));
	        	temp.setXMMC_HIS(result.getString("XMMC_HIS"));
	        	temp.setXMDW(result.getString("XMDW"));
	        	temp.setXMDJ(result.getString("XMDJ"));
	        	temp.setXMSL(result.getString("XMSL"));
	        	temp.setXMJE(result.getString("XMJE"));
	        	temp.setCJSJ(result.getString("CJSJ"));
	        	temp.setJFSJ(result.getString("JFSJ"));
	        	temp.setJSSJ(result.getString("JSSJ"));
	        	temp.setJSCS(result.getString("JSCS"));
	        	temp.setJSPHJ(result.getString("JSPHJ"));
	        	temp.setSFKSS(result.getString("SFKSS"));
	        	temp.setSFGJJY(result.getString("SFGJJY"));
	        	temp.setSFJMYP(result.getString("SFJMYP"));
	       
	        	temp.setBCRQ(result.getString("BCRQ"));
	        	temp.setZRLSH(result.getString("ZRLSH"));
	        	temp.setORGCODE(result.getString("ORGCODE"));
	        	temp.setAREACODE(result.getString("AREACODE"));
	        	temp.setUPLOADTIME(result.getString("UPLOADTIME"));
	        	temp.setSOURCEID(result.getString("SOURCEID"));
	        	re.add(temp);
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if (result != null)
	                result.close();
	            if (pre != null)
	                pre.close();
	            if (con != null)
	                con.close();
	            System.out.println("数据库连接已关闭！");
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
		return re;
	}
}
